Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class Competencia
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        btnIncluir.Enabled = False

        If Not Page.IsPostBack Then

            preencheDDL()

            If Not (Session("AnoDeCompetencia") = "") Then

                ddlAno.SelectedValue = Session("AnoDeCompetencia").ToString

                CarregaGVW()

            End If

        End If

    End Sub

    Private Sub preencheDDL()

        Dim intI As Integer = 0
        Dim intAno As Integer = 0
        Dim intAnoAtual As Integer = 0

        Try
            intAno = 2009

            ddlAno.Items.Insert(0, "")
            ddlAno.Items.Insert(1, intAno)

            intAnoAtual = DateTime.Now.Year

            If ((intAnoAtual - intAno) > 0) Then
                For intI = 1 To (intAnoAtual - intAno)
                    ddlAno.Items.Insert((intI + 1), intAno + intI)
                Next
            End If

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Private Sub CarregaGVW()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT SUBSTRING(MesAnoDeCompetencia,3,4) AS AnoDeCompetencia," & _
                     " LEFT(MesAnoDeCompetencia,2) AS MesDeCompetencia" & _
                     " FROM Competencia" & _
                     " WHERE SUBSTRING(MesAnoDeCompetencia,3,4) = '" & ddlAno.SelectedValue & "'" & _
                     " ORDER BY AnoDeCompetencia, MesDeCompetencia;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                gvwData.DataSource = mydatatable
                gvwData.DataBind()

                If (ddlAno.Text = "") Then
                    btnIncluir.Enabled = False
                Else
                    If (gvwData.Rows.Count = 12) Then
                        btnIncluir.Enabled = False
                    Else
                        btnIncluir.Enabled = True
                    End If
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Protected Sub gvwData_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvwData.PageIndexChanging

        gvwData.PageIndex = e.NewPageIndex
        gvwData.DataBind()

    End Sub

    Protected Sub gvwData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvwData.SelectedIndexChanged

    End Sub



    Protected Sub btnIncluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIncluir.Click

        Session.Add("strOperacao", "I") ' Incluir

        Session.Add("AnoDeCompetencia", ddlAno.SelectedValue)

        Response.Redirect("CompetenciaData.aspx")

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("Default.aspx")

    End Sub

    Protected Sub ddlAno_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlAno.SelectedIndexChanged

        CarregaGVW()

    End Sub

End Class
